<?php
class TheTvDB
{
	private $user;
	/**
	 * @var sql
	 */
	private $db;
	/**
	 * @var tvapi
	 */
	private $api;

	public $listNumHit = 12;
	private $update_days = 7;

	private $searchTerm;
	private $searchTerm_clean;

	private $seriesDb = array();

	function __construct()
	{
		global $db;

		$this->api = new tvapi();
		$this->db = $db;
		$this->user = new users;
	}

	/**
	 * @return tvapi
	 */
	public function getApi()
	{
	    return $this->api;
	}

	private function setSearchTerm($term)
	{
	    $this->searchTerm = $this->db->real_escape_string($term);
	    $this->searchTerm_clean = preg_replace("/[^\w]/", '', $term);
	}

	public function getSeriesList()
	{
		$sql = 'SELECT `id`, `seriesname` FROM `' . SERIES_TABLE . '`';
		$this->db->query($sql);

		foreach($this->db->fetch_assoc() as $item){
			$this->seriesDb[$item['id']] = $item['seriesname'];
		}

		return $this->seriesDb;
	}

	public function isSeriaIsset($seria_id)
	{
        if (empty($this->seriesDb)) {
            $this->getSeriesList();
        }
        return isset($this->seriesDb[$seria_id]);
	}

	public function searchSeries($term, $mode = 1)
	{
		if (strlen($term) < 2) {
			return false;
		}

		$this->setSearchTerm($term);

		switch($mode) {
			default:
			case 1:
				$field = 'seriesname';
				$term = "LIKE '{$this->searchTerm}%'";
			break;
			case 2:
				$field = 'actors';
				$term = "REGEXP '[.|.]{$this->searchTerm}[^[.|.]]*[.|.]'";
			break;
			case 3:
				$field = 'genre';
				$term = "REGEXP '[.|.]{$this->searchTerm}[^[.|.]]*[.|.]'";
			break;
			case 4:
				$field = 'network';
				$term = "LIKE '{$this->searchTerm}%'";
			break;
		}

		$sql = "SELECT `id`,`language`,`seriesname`,`firstaired`
				FROM `" . SERIES_TABLE . "` WHERE `{$field}` {$term}";
		//$sql .= " and (`language` = 'en' or `language` = 'hu')";
		$sql .= " AND `language` = 'en'";
//		$sql .= " and last_updated > SUBDATE(NOW(), {$this->update_days})";
		$sql .= " LIMIT {$this->listNumHit}";

		$this->db->query($sql);

		$result = array();
		if ($this->db->affected_rows() > 0) {
	        $result = $this->db->fetch_assoc();
		} else {
			if ($field == 'seriesname') {
			    try {
    				$result = $this->api->searchSeria($this->searchTerm);
    				//@todo mass insert
    				foreach ($result as $seria) {
    				    $this->insertSeria($seria);
    				}
			    } catch (Exception $e) {
			        //@todo errorlog
			    }
			}
		}

		if (!empty($result)) {
			$this->formatHitData($result);
			usort($result, array($this, 'sortbyRelevance'));
		}

		return $result;
	}

	private function formatHitData(array &$arr)
	{
		sort($arr);
		$result = $arr;
		$arr = array();
		for($i = 0, $c = count($result); $i < $c; $i++){
			$curArr = array('id' => true, 'seriesname' => true, 'firstaired' => true, 'checked' => true);
			if ((
    			  (isset($result[$i-1]['id']) && $result[$i]['id'] == $result[$i-1]['id'])
    			  ||
    			  (isset($result[$i+1]['id']) && $result[$i]['id'] == $result[$i+1]['id'])
			    )
			      && $result[$i]['language'] == 'hu'
			   )
			{
					continue;
			}

			$res = $result[$i];
			foreach($curArr as $key => &$value){
				if ($key == 'checked') {
					$value = $this->isWatched($curArr['id']);
				} else {
					$value = (isset($res[$key])) ? $res[$key] : '';
				}
			}
			array_push($arr, $curArr);
		}
	}

	private function isWatched($id)
	{
		return (bool)($this->user->isLoggedin() && in_array($id, $this->user->data['user_settings']['user_series']));
	}

	public function getSeria($searchFor, $onlySeriaData = false, $byName = false)
	{
	    if ($byName) {
	        $expr = "`seriesname` = '" . $this->db->real_escape_string($searchFor) . "'";
	    } else {
	        $expr = "`id` = '". (int)$searchFor ."'";
	    }

        $sql = "SELECT *, UNIX_TIMESTAMP(`last_updated`) as `last_upd_time` FROM `" . SERIES_TABLE . "`
        		WHERE `language` = 'en' AND " . $expr;

		$this->db->query($sql);
		$result = $this->db->fetch_assoc();
		$serie = isset($result[0]) ? $result[0] : false;
		if ($serie !== false) {
		    $oneWeekBefore = strtotime('-1 week');

		    if ($serie['last_upd_time'] < $oneWeekBefore || $serie['actors'] == null) {
		        try {
    		        $serie_data = $this->api->loadData($serie['id'], 'seria');
    		        $serie = $this->insertSeria($serie_data);
		        } catch (Exception $e) {
		            //@todo errorlog
		        }
		    }

			if ($serie['banner'] != '') {
				$serie['bannermd5'] = md5($serie['banner']);
			}
			$serie['airs_dayofweek'] = strftime('%A', strtotime($serie['airs_dayofweek']));
			$serie['airs_time'] = strftime('%H:%M', strtotime($serie['airs_time']));

			if (!$onlySeriaData) {
			    $ep_sql = "SELECT *, UNIX_TIMESTAMP(`last_updated`) as `last_upd_time`
			    		   FROM `" . EPISODES_TABLE . "`
			    		   WHERE `seriesid` = '{$serie['id']}'
			    		   ORDER BY `seasonnumber`, `episodenumber`";
			    $this->db->query($ep_sql);
			    $eps = (array)$this->db->fetch_assoc();

			    $oneEp = isset($eps[0]) ? $eps[0] : false;
				if ($oneEp === false || $oneEp['last_upd_time'] < $oneWeekBefore) {
				    try {
    				    $epsData = $this->api->loadSeriaAllData($serie['id'], tvapi::EPISODES);
    					$eps = $this->insertEpisodes($epsData['episodes']);
				    } catch (Exception $e) {
				        //@todo errorlog
				    }
				}

			    foreach($eps as &$ep) {
					if ($ep['filename'] != '') {
						$ep['filenamemd5'] = md5($ep['filename']);
					}
				}

				return array('serie' => $serie, 'episodes' => $eps);
			} else {
				return $serie;
			}
		} else {
		    return false;
		}
	}

	public function getSeasonById($id)
	{
	    $sql = "SELECT * FROM `" . EPISODES_TABLE . "` WHERE `seasonid` = '{$id}' ORDER BY `episodenumber`";
		$this->db->query($sql);
		$eps = $this->db->fetch_assoc();
		$rank = new rank;
		foreach($eps as &$item){
			$item['rating'] = $rank->getAvRank($item['id'], 0);
		}

		return $eps;
	}

	public function getSeriesBySeasonById($id)
	{
		$sql = "SELECT `id`,`seriesname` FROM `" . SERIES_TABLE . "`
				WHERE `id` = (SELECT `seriesid` FROM `" . EPISODES_TABLE . "` WHERE `seasonid` = '{$id}' LIMIT 1)";
		$this->db->query($sql);
		list($series) = $this->db->fetch_assoc();

		return $series;
	}

	public function getEpisodes($seria_id)
	{
	    $sql = 'SELECT * FROM `' . EPISODES_TABLE . '` WHERE `seriesid` = ' . (int)$seria_id . '
	    ORDER BY `seasonnumber`, `episodenumber`';
	    $this->db->query($sql);

	    return (array)$this->db->fetch_assoc();
	}

	//@todo kell egy mass insert verzió
	public function insertSeria($seria_data)
	{
		if (empty($seria_data)) {
			throw new Exception('Nincs adat.');
		}

		try {
		    $this->db->array_to_insert(SERIES_TABLE, $seria_data, ', last_updated = CURRENT_TIMESTAMP()', true);
		} catch (Exception $e) {
		    throw $e;
		}

		return $seria_data;
	}

	public function insertEpisode($episode_data)
	{
	    if (empty($episode_data)) {
	        throw new Exception('Nincs adat.');
	    }

	    if ($episode_data['seasonnumber'] != 0 && $this->isSeriaIsset($episode_data['seriesid'])) {
	        try {
	            $this->db->array_to_insert(EPISODES_TABLE, $episode_data, ', last_updated = CURRENT_TIMESTAMP()', true);
	        } catch (Exception $e) {
	            throw $e;
	        }
	        return $episode_data;
	    }

	    return false;
	}

	public function insertEpisodes($episodes_data)
	{
		if (empty($episodes_data)) {
			return false;
		}

		//@todo prepare nem ártana...
		$eps = array();
	    foreach ($episodes_data as $episode) {
			if ($episode['seasonnumber'] != 0 && $this->isSeriaIsset($episode['seriesid'])) {
				$this->db->array_to_insert(EPISODES_TABLE, $episode, ', last_updated = CURRENT_TIMESTAMP()');
				$eps[] = $episode;
			}
		}

		return $eps;
	}

    public function updateSeriesById($seria_id, $what = 3)
	{
	    try {
    		if ($what & tvapi::EPISODES) {
    		    if ($what < 3) $what = 3;
                $data = $this->api->loadSeriaAllData($seria_id, $what);
                $seria_data = $data['seria'];
                $eps_data = $data['episodes'];
    		}

    		if ($what & tvapi::SERIA) {
    		    if (!isset($seria_data)) {
    		        $seria_data = $this->api->loadData($seria_id, 'seria');
    		    }
                $serie = $this->insertSeria($seria_data);
    		}

    		$eps = $what & tvapi::EPISODES ? $this->insertEpisodes($eps_data) : array();

    		return array('serie' => $serie, 'episodes' => $eps);
	    } catch (Exception $e) {
	        //@todo errorlog
	    }
	}

	public function updateAllEpisodes($seria_id)
	{
	    $this->updateSeriesById($seria_id, tvapi::EPISODES);
	}

	private function updateEpisodeById($episode_id)
	{
	    try {
            $eps_data = $this->api->loadData($episode_id, 'episode');

            $eps = $this->insertEpisodes($eps_data);
	    } catch(Exception $e) {
	        //@todo errorlog
	    }

		return array('episodes' => $eps);
	}

	static function getImageNameForMD5($type = 'series', $id)
	{
		global $db;

		switch($type){
			case 'series':
				$sql = "SELECT `banner` FROM `" . SERIES_TABLE . "` WHERE MD5(`banner`) = '{$id}' LIMIT 1";
			break;

			case 'episode':
				$sql = "SELECT `filename` FROM `" . EPISODES_TABLE . "` WHERE MD5(`filename`) = '{$id}' LIMIT 1";
			break;
		}

		if (isset($sql)){
			if (!$db->query($sql) && $db->affected_rows() != 1)
				return false;
			list($name) = $db->fetch_row();

			return $name[0];
		}
		else {
			return false;
		}
	}

	protected function filterAry($ary, array $filter)
	{
	    $filtered = array();
	    foreach ($filter as $f) {
	        if (isset($ary[$f])) {
	            $filtered[$f] = $ary[$f];
	        }
	    }
	    return $filtered;
	}

	public function getCalendar($year, $month, $showAll = false)
	{
		$rank = new rank;
		$epNeeded = array('id', 'episodename', 'episodenumber', 'firstaired', 'overview', 'seasonnumber', 'filename', 'seriesid', 'series');
		$seriaNeeded = array('id', 'seriesname');

		$weekPh = PHP_OS == 'WINNT' ? '%W' : '%V';

		$week = strftime($weekPh, strtotime("{$year}-{$month}-01"));
		$yearC = $week > 52 ? $year-1 : $year;
		if($month == 1 && $yearC == $year && $week == 52)
			$yearC = $year-1;

		$start = strftime("%Y-%m-%d", strtotime(strftime("{$yearC}-W{$weekPh}-1", strtotime("{$year}-{$month}-01"))));
		$end = strftime("%Y-%m-%d", strtotime("{$start} -1 day +6 weeks"));

		$showOnNextDay = (isset($this->user->data['user_settings']['preferences']) && !(int)$this->user->data['user_settings']['preferences']['original_date']);

		$sql = "SELECT";
		$sql .= ($showOnNextDay) ? " *, DATE_ADD(`firstaired`, INTERVAL 1 DAY) as `firstaired`" : " * ";
		$sql .= "FROM `" . EPISODES_TABLE . "` WHERE";
		$sql .= ($showOnNextDay) ? " DATE_ADD(`firstaired`, INTERVAL 1 DAY)" : " `firstaired`";
		$sql .= " BETWEEN '{$start}' and '{$end}'";
		if (!$showAll && $this->user->isLoggedin() && is_array($this->user->data['user_settings']['user_series']) && count($this->user->data['user_settings']['user_series']))
		{
			$sql .= " and `seriesid` IN(";
			$serids = '';
			foreach($this->user->data['user_settings']['user_series'] as $serid) {
				if (strlen($serids))
					$serids .= ", ";
				$serids .= $serid;
			}
			$sql .= "{$serids})";
		}

		$sql .= ' ORDER BY `firstaired` ASC';
		$result = $this->db->query($sql);
		$match = $this->db->fetch_assoc();

    $days = array();
		while ($start <= $end) {
			$days[$start] = array();
			$start = strftime('%Y-%m-%d', strtotime($start . ' + 1 day'));
		}

		$notWatchingDays = $days;

		if (is_array($match)) {
    		foreach($match as &$ep) {
    		    if ($this->isSeriaIsset($ep['seriesid'])) {
    		        $series = $this->getSeria($ep['seriesid'], true);
    		        $ep['series'] = $this->filterAry($series, $seriaNeeded);
    		    }

    		    if (isset($ep['series']) && !empty($ep['series']['seriesname'])) {
        			foreach($ep as $key => &$item) {
        				if(!in_array($key, $epNeeded)) {
        					unset($ep[$key]);
        					continue;
        				}
        				switch($key) {
        					case 'filename':
        						$item = (!empty($item)) ? md5($item) : null;
        					break;
        				}
        			}
        			$ep['rank'] = (strtotime($ep['firstaired']) < strtotime(strftime('%Y-%m-%d'))) ? $rank->getRank($ep['id']) : false;
        			$ep['checked'] = $this->isWatched($ep['series']['id']);

							if($showAll && $this->user->isLoggedin() && !$ep['checked'])
								$notWatchingDays[$ep['firstaired']][] = $ep;
							else
        				$days[$ep['firstaired']][] = $ep;
    		    }
    		}

				if(count($notWatchingDays))
					$days = array_merge_recursive($days, $notWatchingDays);
		}
		return $days;
	}

	public function getContinuingSeries($plususers = false)
	{
		setLocale(LC_COLLATE, 'C');

		$ids = '';
		if ($this->user->isLoggedin() and $plususers ===  true) {
			foreach($this->user->data['user_settings']['user_series'] as $item)
				$ids .= "OR `id` = '{$item}' ";
		}

		$sql = "SELECT * FROM `" . SERIES_TABLE . "` WHERE `status` = 'Continuing' {$ids} ORDER BY `seriesname`";
		if ($this->db->query($sql) && $this->db->affected_rows()) {
			$result = $this->db->fetch_assoc();

			$this->formatHitData($result);

			usort($result, array($this, 'sortBySeriesName'));
		} else {
			$result = array();
		}

		return $result;
	}

	public function mostPopularSeries()
	{
		$sql = "SELECT s.id, count(f.series_id) as `hits`, s.`seriesname` FROM `" . FOLLOWS_TABLE . "` f RIGHT JOIN `" . SERIES_TABLE . "` s on f.`series_id` = s.`id` GROUP BY f.`series_id` ORDER BY `hits` DESC, s.seriesname ASC LIMIT 5";
		$this->db->query($sql);

		return $this->db->fetch_assoc();
	}

	public function removeSeriesById($id)
	{
		$sql = "DELETE f FROM `" . FOLLOWS_TABLE . "` f LEFT JOIN `" . SERIES_TABLE . "` s on s.id = f.series_id  WHERE s.`id` = '{$id}';";
		$sql .= "DELETE FROM `" . SERIES_TABLE . "` WHERE `id` = '{$id}';";
		$sql .= "DELETE r FROM `" . RANKING_TABLE . "` r LEFT JOIN `" . EPISODES_TABLE . "` e on e.id = r.episode_id  WHERE e.`seriesid` = '{$id}';";
		$sql .= "DELETE r FROM `" . REVIEW_TABLE . "` r LEFT JOIN `" . EPISODES_TABLE . "` e on e.id = r.episode_id  WHERE e.`seriesid` = '{$id}';";
		$sql .= "DELETE FROM `" . EPISODES_TABLE . "` WHERE `seriesid` = '{$id}';";

		return $this->db->multi_query($sql);
	}

	public function getSeriaById($seria_id)
	{
	    $sql = 'SELECT * FROM ' . SERIES_TABLE . '
	    WHERE id =' . (int)$seria_id . " AND language = '". $this->api->getLanguage() ."'";
	    $this->db->query($sql);
	    $result = $this->db->fetch_assoc();
	    if (isset($result[0])) {
	        return $result[0];
	    } else {
	        throw new Exception($seria_id . ' sorozat nem található az adatbázisban.');
	    }
	}

	public function getSeriesByIds(array $ids, $orderByName = false)
	{
	    $idlist = implode(',', $ids);
	    $sql = 'SELECT * FROM ' . SERIES_TABLE . ' ' .
	    "WHERE `id` IN ({$idlist}) AND `language` = '" . $this->api->getLanguage() . "'";
	    if ($orderByName) {
	        $sql .= ' ORDER BY `seriesname`';
	    }
	    $this->db->query($sql);

	    $result = $this->db->fetch_assoc();
	    $return = array();
	    if (!empty($result)) {
    	    foreach ($result as $row) {
    	        $return[$row['id']] = $row;
    	    }
	    }
	    return $return;
	}

    public function getEpisodeById($episode_id)
	{
	    $sql = 'SELECT s.id as seria_id, s.seriesname, e.*
	    FROM ' . EPISODES_TABLE . ' e LEFT JOIN ' . SERIES_TABLE . ' s ON e.id = s.id
	    WHERE e.id=' . (int)$episode_id . " AND language = '". $this->api->getLanguage() ."'";
	    $this->db->query($sql);
	    $result = $this->db->fetch_assoc();
	    if (isset($result[0])) {
	        return $result[0];
	    } else {
	        throw new Exception($episode_id . ' epizód nem található az adatbázisban.');
	    }
	}

	public function getEpisodesByIds(array $ids, $orderByName = false)
	{
	    $idlist = implode(',', $ids);
	    $sql = 'SELECT s.id as seria_id, s.seriesname, e.*
	    FROM ' . EPISODES_TABLE . ' e
	    LEFT JOIN ' . SERIES_TABLE . ' s ON e.seriesid = s.id
	    WHERE e.id IN (' . $idlist . ") AND e.language = '". $this->api->getLanguage() ."'";
	    if ($orderByName) {
	        $sql .= ' ORDER BY s.seriesname, e.episodename';
	    }
	    $this->db->query($sql);

	    $result = $this->db->fetch_assoc();
	    $return = array();
	    if (!empty($result)) {
    	    foreach ($result as $row) {
    	        $return[$row['id']] = $row;
    	    }
	    }
	    return $return;
	}


    private function sortByRelevance($a, $b)
	{
		$amatch = preg_replace("/^(.*){$this->searchTerm_clean}.*$/i", "$1", $a['seriesname']);
		$bmatch = preg_replace("/^(.*){$this->searchTerm_clean}.*$/i", "$1", $b['seriesname']);
		$match = strlen($amatch)-strlen($bmatch);
		return ($match >= 0) ? ($match == 0) ? 0 : 1 : -1;
	}

	static function sortBySeriesName($a, $b)
	{
		$aa = mb_strtolower($a['seriesname'], 'utf-8');
		$bb = mb_strtolower($b['seriesname'], 'utf-8');

		$accents = array("/^the /i", "/ö/i", "/ü/i", "/ó/", "/ő/", "/ú/", "/é/i", "/á/i", "/ű/", "/í/", "/Í/", "/Ó/", "/Ű/", "/Ú/", "/Ő/");
		$normal = array("", "o", "u", "o", "o", "u", "e", "a", "u", "i", "I", "O", "U", "U", "O");
		$aa = preg_replace($accents, $normal, $aa);
		$bb = preg_replace($accents, $normal, $bb);

		if(preg_match('/^[^a-zA-Z]/', $aa))
			$aa = 'zzzzzzzzzzz' . $aa;
		if(preg_match('/^[^a-zA-Z]/', $bb))
		$bb = 'zzzzzzzzzzz' . $bb;

        return strcmp($aa, $bb);
	}
}
?>